using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Net;
using System.Xml;
using System.IO;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management;
using System.Reflection;
using System.Threading;
using System.Configuration;




namespace AutoUpdate
{

    public partial class FrmAutoUpdate : Form
    {
        //private String conStr3 = @"Data Source=localhost;Initial Catalog = master; User ID=sa;Password=Ccpcfrdc31031617;Net=dbmssocn;Connection Timeout=15;";
        //private String conStr2 = @"Data Source=localhost;Initial Catalog = master;integrated security=SSPI;Net=dbmssocn;Connection Timeout=15;";
        //private String conStr = @"Data Source=localhost;Initial Catalog = master;integrated security=SSPI;Net=dbmslpcn;Connection Timeout=15;";
         
        private String DatabaseName = String.Empty;
        private String ServerName = String.Empty;
        //private Boolean se_instaleaza = true;
        private String BackupDirectory = String.Empty;
        private String nume = null;
        private XmlDocument m_xmld = new XmlDocument(),xmlSF = new XmlDocument();
        private String proxyServer = null;
        private Int32 proxyPort = 0;
        Boolean FinalizareAutomata = true;
        private List<String>listaExecute = new List<String>();
        private List<String> listaScripturi = new List<String>();
        public System.Configuration.Configuration csSection = null;
        //private String numeInst = "SQLEXPRADV_x86_ENU.exe";
        public Boolean SeInstaleazaSQl = false;

        public FrmAutoUpdate(String vNume, String numeProxy, Int32 portProxy, Boolean fin, System.Configuration.Configuration _csSection)
        {
            csSection = _csSection;
            nume = vNume;
            proxyServer = numeProxy;
            proxyPort = portProxy;
            FinalizareAutomata = fin;
            InitializeComponent();
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            timer1.Enabled = false;
            ProcessUpdate(this, null);

        }
        public void Start(String nume_mod)
        {
            try
            {
                //presupunem ca nu are parametrii
                ProcessStartInfo startInfo = new ProcessStartInfo(nume_mod);
                //startInfo.Arguments = Environment.CommandLine;
                Process.Start(startInfo);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        public int KillAppExe(String nume_app)
        { 
            /// Get MainApp exe name without extension
            /// 
            if (nume_app ==null)
                return 0;
            String AppExe = nume_app.Replace(".exe", "");

            Process[] local = Process.GetProcesses();
            int i = 0, era = 0; ;
            /// Search MainApp process in windows process
            /// 
            for(i = 0;i < local.Length - 1; i++)
            {
                /// If MainApp process found then close or kill MainApp
                if (local[i].ProcessName.ToUpper().Contains(AppExe.ToUpper()))
                {
                    local[i].Kill();
                    era++;
                    if (listBox1!=null)
                        listBox1.Items.Add("Proces oprit: " + local[i].ProcessName.ToUpper(), 3);
                }

            }
            return era;
        }

        public void copyDirectory(string Src,string Dst,Boolean princ)
        {
            String[] Files;

            if(Dst[Dst.Length-1]!=Path.DirectorySeparatorChar) 
                Dst+=Path.DirectorySeparatorChar;
            if(!Directory.Exists(Dst)) 
                Directory.CreateDirectory(Dst);
            Files=Directory.GetFileSystemEntries(Src);
            if (princ)
            {
                ProgressBar1.Maximum = Files.Length;
                ProgressBar1.Value = 0;
                Application.DoEvents();
            }
            foreach(string Element in Files){
                // Sub directories
                if (Directory.Exists(Element))
                {
                    if (Element.ToLower().Contains(".backup") || Element.ToLower().Contains("scanari"))
                        continue;
                    copyDirectory(Element, Dst + Path.GetFileName(Element),false);
                }
                // Files in directory
                else
                {
                    //nu copiaza elementul curent
                    if (Element.ToLower().Contains("autoupdate.exe") || Element.ToLower().Contains("trace.log") || Element.ToLower().Contains("statusfile.xml") || Element.ToLower().Contains(".xip") || Element.ToLower().Contains(".bak"))
                        continue;
                    try
                    {
                        File.Copy(Element, Dst + Path.GetFileName(Element), true);
                    }
                    catch (Exception e)
                    {
                        Trace.WriteLine("[" + DateTime.Now.ToString() + "] Copiere fisier: " + e.Message);
                    }
                }
                if (princ)
                {
                    ProgressBar1.PerformStep();
                    Application.DoEvents();
                }
            }
        }

        public void deleteDirectory(string Dst)
        {
            if (Dst[Dst.Length - 1] != Path.DirectorySeparatorChar)
                Dst += Path.DirectorySeparatorChar;
            if (!Directory.Exists(Dst))
                return;
            // ...or with DirectoryInfo instance method.
            DirectoryInfo di = new DirectoryInfo(Dst);
            // Delete this dir and all subdirs.
            try
            {
                di.Delete(true);
            }
            catch (Exception e)
            {
                Trace.WriteLine("[" + DateTime.Now.ToString() + "] Clear directory: " + e.Message);
            }

        }
    
        private void ProcessUpdate(object sender, EventArgs e)
        {
            ActualizeazaTSB.Enabled= false;
            this.listBox1.Items.Clear();
            //listBox1.Items.
            WebClient myWebClient = new WebClient();
            /// Download manifest file
            try
            {
                
                //get the update file content in manifest file
                Label2.Text = "download manifest...";
                Application.DoEvents();
                WebProxy wProxy = new WebProxy();
                if (proxyServer != String.Empty)
                {
                    String addrP = proxyServer + ":" + proxyPort.ToString();
                    wProxy.Address = new Uri(addrP);
                }
                wProxy.Credentials = CredentialCache.DefaultCredentials;
                myWebClient.Proxy = wProxy;
                myWebClient.BaseAddress = Program.RemoteUri;
                myWebClient.CachePolicy = new System.Net.Cache.RequestCachePolicy(System.Net.Cache.RequestCacheLevel.NoCacheNoStore);
                ///myWebClient.DownloadData("http://www.datastudio.ro");
                ///
                Trace.WriteLine("[" + DateTime.Now.ToString() + "] Download fisier: " + Program.RemoteUri + "(" + Program.ManifestFile +")");
                myWebClient.DownloadFile(Program.ManifestFile, Program.ManifestFile);
                Label2.Text = "Finalizare download manifest!";

                Application.DoEvents();

            }
            catch(Exception ex)
            {
                if (ex.Message.IndexOf("(404)")>=0)
                {
                    this.listBox1.Items.Add("Lipsa fisier manifest! Verificati existenta lui cu browserul dvs. preferat!");
                    Application.DoEvents();
                    ActualizeazaTSB.Enabled= true;
                    return;
                }
                if (ex.Message.IndexOf("not be resolved") >= 0)
                {
                    this.listBox1.Items.Add("Situl actualizarilor nu poate fi contactat. Va rugam sa incercati peste 30 minute!");
                    Application.DoEvents();
                    ActualizeazaTSB.Enabled= true;
                    return;
                }

                if (ex.Message.IndexOf("(1000)") >= 0)
                {
                    this.listBox1.Items.Add(ex.Message);
                    Application.DoEvents();
                    ActualizeazaTSB.Enabled= true;
                    return;
                }

                MessageBox.Show(Program.ManifestFile + ":\n" + ex.ToString());
                ActualizeazaTSB.Enabled=true;
                return;
            }   

            //face backup la directorul curent
            

            XmlNodeList m_nodelist,statusFile;
             ///Load the Xml file
            m_xmld.Load(Directory.GetCurrentDirectory() + @"\" + Program.ManifestFile);
            //incarca starea fisierelor
            m_nodelist = m_xmld.SelectNodes("/update/name");
            InitProgress(m_nodelist.Count);
            XmlNode parent = m_xmld.SelectSingleNode("/update");
            DateTime dtu = DateTime.FromFileTimeUtc(Convert.ToInt64(parent.Attributes["DataPublicare"].Value));
            XmlNode parentSL = null;
            if (File.Exists(Directory.GetCurrentDirectory() + @"\StatusFile.xml"))
            {
                xmlSF.Load(Directory.GetCurrentDirectory() + @"\StatusFile.xml");
                statusFile = xmlSF.SelectNodes("/update/name");

                parentSL = xmlSF.SelectSingleNode("/update");
                if (parentSL.Attributes.Count !=0)
                {
                    DateTime dtl = DateTime.FromFileTimeUtc(Convert.ToInt64(parentSL.Attributes["DataPublicare"].Value));
                    if (dtl >= dtu)
                    {
                        if (FinalizareAutomata)
                            this.Close();
                        return;
                    }
                 }
            }
            if (parent != null)
            {
                //salveaza directorul curent
                BackupDirectory = Directory.GetCurrentDirectory();
                if (BackupDirectory[BackupDirectory.Length - 1] != Path.DirectorySeparatorChar)
                    BackupDirectory += Path.DirectorySeparatorChar;
                BackupDirectory += parent.Attributes["Arhiva"].Value.Replace("xip","backup") ;
                Label2.Text = "salveaza datele existente...";
                Application.DoEvents();
                Trace.WriteLine("[" + DateTime.Now.ToString() + "] Backup directory: " +BackupDirectory);
                copyDirectory(Directory.GetCurrentDirectory(), BackupDirectory,true);

                loopNodeSetup(parent.ChildNodes);
                loopNode(Directory.GetCurrentDirectory(), Program.RemoteUri, parent.Attributes["Arhiva"].Value);
                
            }            
        }

        private String RealFileName = String.Empty;
        private Boolean loopNode(String path, String webAddress, String fileNameValue)
        {
            WebClient myWebClient = new WebClient();
            myWebClient.BaseAddress = webAddress;
            myWebClient.DownloadFileCompleted += new AsyncCompletedEventHandler(myWebClient_DownloadFileCompleted);
            myWebClient.DownloadProgressChanged += new DownloadProgressChangedEventHandler(myWebClient_DownloadProgressChanged);
            WebProxy wProxy = new WebProxy();
            if (proxyServer != String.Empty)
            {
                String addrP = proxyServer + ":" + proxyPort.ToString();
                wProxy.Address = new Uri(addrP);
            }

            myWebClient.Proxy = wProxy;
            wProxy.Credentials = CredentialCache.DefaultCredentials;
            myWebClient.CachePolicy = new System.Net.Cache.RequestCachePolicy(System.Net.Cache.RequestCacheLevel.NoCacheNoStore);
            ListViewItem item1 = null;
            try
            {
                item1 = listBox1.Items.Add(fileNameValue);
                String TempFileName = path + @"\" + DateTime.Now.TimeOfDay.TotalMilliseconds;
                RealFileName = path + @"\" + fileNameValue;
                Label2.Text = "Update file " + fileNameValue + "...";
                Application.DoEvents();

                if (File.Exists(RealFileName))
                {
                    File.Delete(RealFileName);
                    //File.Copy(RealFileName, TempFileName, true);
                    //File.Delete(TempFileName);
                }
                ProgressBar1.Maximum = 100;
                myWebClient.DownloadFileAsync(new Uri(webAddress+fileNameValue), RealFileName);
                Trace.WriteLine("[" + DateTime.Now.ToString() + "] Download fisier: " + webAddress + "(" + fileNameValue + ")");
                item1.SubItems.Add("OK");
                item1.ImageIndex = 1;
                
           }
           catch (Exception ex)
           {
               if (ex.Message.IndexOf("(404)")>=0)
               {
                   //MessageBox.Show(ex.ToString());
                   item1 = this.listBox1.Items.Add(fileNameValue);
                   item1.SubItems.Add("Indisponibil");
                   item1.ImageIndex = 0;
                   return false;
               }
               if (ex.Message.IndexOf("(1000)") >= 0)
               {
                   //MessageBox.Show(ex.ToString());
                   this.listBox1.Items.Add(fileNameValue + "...format versiune incorect(nu are 4 cifre)!");
                   item1.ImageIndex = 0;
                   return false;
               }
               //item1 = this.listBox1.Items.Add(fileNameValue);
               item1.SubItems.Add("Eroare: " + ex.Message );
               item1.ImageIndex = 0;
               Trace.WriteLine("[" + DateTime.Now.ToString() + "] Eroare: " + ex.Message);
               return false;
           }
            return true;
        }


        void myWebClient_DownloadProgressChanged(object sender, DownloadProgressChangedEventArgs e)
        {
            if (ProgressBar1.Value < ProgressBar1.Maximum)
            {
                ProgressBar1.Value = e.ProgressPercentage;
                Application.DoEvents();
            }

        }

        void myWebClient_DownloadFileCompleted2(object sender, AsyncCompletedEventArgs e)
        {
            //InstallAndRestore();
        }

        void myWebClient_DownloadFileCompleted(object sender, AsyncCompletedEventArgs e)
        {
            Trace.WriteLine("[" + DateTime.Now.ToString() + "]{AutoUpdate} End Download fisier: " + RealFileName);
            Trace.Flush();
            Boolean ret = true;
            if (RealFileName != String.Empty)
            {
                Trace.WriteLine("[" + DateTime.Now.ToString() + "]{AutoUpdate} procesare fisier: " + RealFileName);
                Trace.Flush();
                ret = ProcessArchive(RealFileName);
                
                Trace.WriteLine("[" + DateTime.Now.ToString() + "] AutoUpdate stare procesare arhiva: " + (ret ? "Ok" : "Eroare"));
                Trace.Flush();
                
                if (File.Exists(Directory.GetCurrentDirectory() + @"\StatusFile.xml"))
                    File.Delete(Directory.GetCurrentDirectory() + @"\StatusFile.xml");
                //redenumeste pe cel adus
                File.Move(Directory.GetCurrentDirectory() + @"\" + Program.ManifestFile, Directory.GetCurrentDirectory() + @"\StatusFile.xml");

                Label1.Text = "Actualizarea aplicatiei este finalizata!";
                ProgressBar1.Value = ProgressBar1.Maximum;
                Application.DoEvents();
                ActualizeazaTSB.Enabled = true;

                if (!ret)
                {
                    copyDirectory(BackupDirectory, Directory.GetCurrentDirectory(), true);
                    deleteDirectory(BackupDirectory);

                }
                else
                {
                    DirectoryInfo di = new DirectoryInfo(BackupDirectory);
                    //ascunde directorul de backup
                    di.Attributes |= FileAttributes.Hidden;
                }
                
                ///daca se instaleaza serverul nu inchide macheta decat in run script...
                ///
                Trace.WriteLine("[" + DateTime.Now.ToString() + "] myWebClient_DownloadFileCompleted terminata");
                if (FinalizareAutomata && !SeInstaleazaSQl)
                {
                    this.Close();
                }
                
            }
        }


        private void loopNodeSetup(XmlNodeList m_nodelist)
        {
            String fileNameValue = null;
            try
            {
                foreach (XmlNode m_node in m_nodelist)
                {

                    if (m_node.Attributes.GetNamedItem("directory") != null)
                    {
                        String fileNameValue1 = m_node.Attributes.GetNamedItem("directory").Value;
                        if (fileNameValue1 != String.Empty)
                        {
                            //m_nodelist = m_node.SelectNodes(xPath + "/name");
                            loopNodeSetup(m_node.ChildNodes);
                            continue;
                        }
                    }

                    if (m_node.Attributes.GetNamedItem("file") == null)
                        return;
                    ///Get the file Attribute Value
                    String fileAttribute = m_node.Attributes.GetNamedItem("file").Value;
                    ///Get the fileName Element Value
                    fileNameValue = m_node.ChildNodes.Item(0).InnerText;
                    ///Get the fileVersion Element Value
                    String fileVersionValue = m_node.ChildNodes.Item(1).InnerText;
 
                    ///Get the fileLastModified Value
                    String fileLastModiValue = m_node.ChildNodes.Item(2).InnerText;

                    // 1 executa script
                    // 2 executabil il lanseaza
                    String strExecute = m_node.ChildNodes.Item(3).InnerText;
                    String paramCmd = m_node.ChildNodes.Item(4).InnerText;
                    String userStr = m_node.ChildNodes.Item(5).InnerText;
                    String parolaStr = m_node.ChildNodes.Item(6).InnerText;
                    if (strExecute == "1")
                    {
                        listaExecute.Add(fileNameValue + "{[(" + paramCmd + ")]}([{" + userStr + "}])[({" + parolaStr + "})]");
                    }
                    else
                    if (strExecute == "2")
                    {
                        listaScripturi.Add(fileNameValue);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }

        private Boolean ProcessArchive(String numeFile)
        {
            Trace.WriteLine("[" + DateTime.Now.ToString() + "]{AutoUpdate} open zip file: " + numeFile);
            Trace.Flush();
            ZipFile archive = new ZipFile(this,numeFile,listaExecute,listaScripturi,csSection);
            Trace.WriteLine("[" + DateTime.Now.ToString() + "]{AutoUpdate} ExtractAll: " + numeFile);
            Trace.Flush();
            Boolean ret = false;
            if (archive == null)
            {
                Trace.WriteLine("[" + DateTime.Now.ToString() + "]{AutoUpdate} ProcessArchive:  null archive object!");
                Trace.Flush();
                return false;
            }
            if (listBox1 == null)
            {
                Trace.WriteLine("[" + DateTime.Now.ToString() + "]{AutoUpdate} ProcessArchive:  null listBox1 object!");
                Trace.Flush();
                return false;
            }
            try
            {
                ret = archive.ExtractAll(".", this.listBox1);
            }
            catch (Exception ex)
            {
                Trace.WriteLine("[" + DateTime.Now.ToString() + "]{AutoUpdate} ProcessArchive Exception: " + ex.Message);
                Trace.Flush();
                return ret;
            }


            Trace.WriteLine("[" + DateTime.Now.ToString() + "]{AutoUpdate} End ProcessArchive: " + numeFile);
            Trace.Flush();
            return ret;
        }


        private void InitProgress(int lMax)
        {
            ProgressBar1.Value = 0;
            ProgressBar1.Maximum = lMax;
            Application.DoEvents();        
        }
        private Double GetVersion(String Version)
        {
            String[] x = Version.Split('.');
            if (x.Length < 4)
            {
                throw new Exception("Format versiune fisier incorect format din mai putin de 4 cifre!(1000)");
            }
            return Convert.ToDouble(x[0])*Math.Pow(10,18)
                + Convert.ToDouble(x[1])*Math.Pow(10,12) +
                Convert.ToDouble(x[2])*Math.Pow(10,6) +
                Convert.ToDouble(x[3]);
        }
        private void IncrementProgress()
        {

            if (ProgressBar1.Value < ProgressBar1.Maximum)
            {
                ProgressBar1.Value++;
                Application.DoEvents();
            }
        }

        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void FrmAutoUpdate_Load(object sender, EventArgs e)
        {
            ActualizeazaTSB.Enabled = false;
        }

        private void toolStripButton2_Click_1(object sender, EventArgs e)
        {
            ProcessUpdate(this, null);
        }

        private void toolStripButton1_Click_1(object sender, EventArgs e)
        {
            this.Close();
        }


        public Boolean RunScript(String numeScr)
        {
            Boolean ret = true;
            ConnectionStringsSection csSec = csSection.ConnectionStrings;
            if (csSec == null)
            {
                Trace.WriteLine("[" + DateTime.Now.ToString() + "] Lipsa sectiune siruri de conectare!");
                return false;
            }

            Text = "Deschide scriptul...";
            Trace.WriteLine("[" + DateTime.Now.ToString() + "] AutoUpdate: " + " Start executie script!");
            StreamReader reader = new StreamReader(numeScr);
            if (reader == null)
            {
                MessageBox.Show("Nu pot citi fisierul: " + numeScr);
                Trace.WriteLine("[" + DateTime.Now.ToString() + "] nu pot accesa fisierul:" + numeScr);
                return false;
            }
            String ScriptText = reader.ReadToEnd();
            reader.Close();
            Trace.WriteLine("[" + DateTime.Now.ToString() + "] AutoUpdate: " + "Script transferat in memorie!");
            String[] SqlLine;
            /// prima linie este cu sirurile de conectare care trebuie folosite
            /// 

            SqlLine = ScriptText.Split('\n');
            //pe prima linie pe care scrie ceva sunt sirurile de conectare
            String siruriConectare = String.Empty;
            foreach (String linii in SqlLine)
            {
                if (linii.Trim() != String.Empty)
                {
                    siruriConectare = linii;
                    break;
                }
            }

            //eliminare comentarii
            siruriConectare = siruriConectare.Replace("/*", null);
            siruriConectare = siruriConectare.Replace("*/", null);
            siruriConectare = siruriConectare.Replace("\r", null);
            ///sirurile sunt separate prin virgula
            String[] sirCon = siruriConectare.Split(',');
            Trace.WriteLine("[" + DateTime.Now.ToString() + "] AutoUpdate: " + " siruri de conectare" + siruriConectare);
            Label1.Text = "Separa sirul de conectare...";

            Regex regex = new Regex("^GO\\s", RegexOptions.IgnoreCase | RegexOptions.Multiline);

            SqlLine = regex.Split(ScriptText);
            ///bazele de date prelucrate ca sa nu le rulez de 2 ori
            List<String> dbPrelucrate = new List<String>();
            List<String> strVerificate = new List<String>();
            SqlConnection conn = new SqlConnection();
            int maxVers = 0;

            for (int i = 0; i < csSec.ConnectionStrings.Count; i++)
            {
                ConnectionStringSettings cs = csSec.ConnectionStrings[i];
                if (cs == null || cs.Name == "LocalSqlServer")
                    continue;
                if (sirCon.Length > 0)
                {
                    Boolean gasit = false;
                    foreach (String sir in sirCon)
                        if (cs.Name == sir)
                        {
                            gasit = true;
                            Trace.WriteLine("[" + DateTime.Now.ToString() + "] AutoUpdate: " + " se foloseste sirul de conectare" + sir);
                            break;
                        }
                    if (!gasit)
                        continue;
                }

                ///La varianta cu sir de conectare explicit nu este necesar un timeout mic
                ///poate eventual mai mare-----------------
                ///conn.ConnectionString = cs.ConnectionString.Replace("Connection Timeout = 15;", "Connection Timeout = 2;");
                ///conn.ConnectionString = conn.ConnectionString.Replace("Connection Timeout=15;", "Connection Timeout = 2;");
                ///daca sirul a fost verificat si nu s-a reusit conectarea 
                ///atunci nu se mai incearca inca o data
                ///
                conn.ConnectionString = cs.ConnectionString;
                if (strVerificate.Contains(conn.ConnectionString))
                    continue;
                while (conn.State != System.Data.ConnectionState.Open)
                {

                    try
                    {
                        Label2.Text = "Conectare cu: " + cs.Name;
                        Trace.WriteLine("[" + DateTime.Now.ToString() + "] AutoUpdate: " + " conectare cu " + cs.Name);
                        Application.DoEvents();
                        conn.Open();
                        DBVersion = GetDataBaseVersion(conn);
                        if (DBVersion != -1)
                            maxVers = DBVersion;
                    }
                    catch (Exception ex)
                    {
                        ///trece la sirul urmator de conectare
                        ///
                        Trace.WriteLine("[" + DateTime.Now.ToString() + "] Eroare deschidere:" + ex.Message);
                        /*strVerificate.Add(conn.ConnectionString);
                        if (conn.State == System.Data.ConnectionState.Open)
                            conn.Close();*/
                        FrmLogin frm = new FrmLogin(cs.Name,String.Empty);
                        DialogResult retD = frm.ShowDialog();
                        if (frm.InstaleazaSQL)
                        {
                            SeInstaleazaSQl = true;
                            ret = false;
                            return ret;
                            // nu mai ruleaza scriptul deocamdata
                        }
                        if (retD == DialogResult.Cancel)
                        {
                            ret = false;
                            return ret;
                        }
                        conn.ConnectionString = frm.ConnectionString;

                    }
                }

                if (dbPrelucrate.Contains(conn.Database + "[" + conn.DataSource + "]") /* || (DBLocalVersion >= DBVersion)*/)
                {
                    if (conn.State == System.Data.ConnectionState.Open)
                        conn.Close();
                    Trace.WriteLine("[" + DateTime.Now.ToString() + "] - sir prelucrat:" + conn.DataSource.ToString());
                    continue;
                }

                BlockDataBase(conn, true);
                SqlCommand cmd = new SqlCommand("", conn);
                SqlTransaction tra = null;

                //tra = conn.BeginTransaction();
                cmd.Transaction = tra;
                foreach (String line in SqlLine)
                {
                    if (line.Length > 0)
                    {
                        cmd.CommandText = line;
                        cmd.CommandType = System.Data.CommandType.Text;
                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            Trace.WriteLine("[" + DateTime.Now.ToString() + "] Eroare la linia:" + line + ";" + ex.ToString());
                            Trace.Flush();
                            //if (tra != null)
                            //tra.Rollback();
                            //ret = false;
                            //break;
                            //daca am erori continui
                            continue;
                        }
                    }
                }
                if (ret)
                {
                    //if (tra != null)
                    //    tra.Commit();
                    cmd.Transaction = null;
                }

                BlockDataBase(conn, false);
                dbPrelucrate.Add(conn.Database + "[" + conn.DataSource + "]");
                if (conn.State == System.Data.ConnectionState.Open)
                    conn.Close();
            }
            //csSection.AppSettings.Settings["DBVersion"].Value = maxVers.ToString(); ;
            //csSection.Save(ConfigurationSaveMode.Full);
            Trace.WriteLine("[" + DateTime.Now.ToString() + "] AutoUpdate: " + "End Executie Script!");

            return ret;
        }

        
        //Please use Windows Update to check for any critical updates to the .NET Framework.
        //Va rugam verificati daca nu este necesara actualizarea componenetelor Microsoft prin Microsoft Update.
        private String GetRestorePath(String strConn)
        {
            String path = String.Empty;
            SqlConnection sqlCon = new SqlConnection(strConn);
            sqlCon.Open();
            SqlCommand cmd = sqlCon.CreateCommand();
            cmd.Connection = sqlCon;
            cmd.CommandText = "SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1) " +
                  " FROM master.sys.master_files " +
                  " WHERE database_id = 1 AND file_id = 1";
            cmd.CommandType = CommandType.Text;
            Object ret = cmd.ExecuteScalar();
            sqlCon.Close();
            cmd.Dispose();
            sqlCon.Dispose();
            //MessageBox.Show(ret.ToString());
            if (ret != null && ret != DBNull.Value)
                path = ret.ToString();
            return path;
        }

        private String updateParamVal(String cmd,String strConn)
        {
            string catCurent = Directory.GetCurrentDirectory() + @"\Data\";
            cmd = cmd.Replace("#databasePath#", catCurent);
            cmd = cmd.Replace("#restorePath#", GetRestorePath(strConn));
            return cmd;
        }

        private string GetLogin(string databaseServer, string userName, string userPass, string database)
        {
            String ret = "Persist Security Info=True;Data Source=" + databaseServer + ";Initial Catalog = " + database + "; User ID=" + userName +
                ";Password=" + userPass + ";";
            //"server=" + databaseServer + ";database=" + database + ";User ID=" + userName + ";Password=" + userPass;
            if (userName == String.Empty)
                ret = "Persist Security Info=True;Data Source=" + databaseServer + ";Initial Catalog = " + database + ";Integrated Security=True;";
            // MessageBox.Show(ret);
            return ret;
        }

        private void RestartServer(String numeServer)
        {
            /*//Declare and create an instance of the ManagedComputer object that represents the WMI Provider services.
            ManagedComputer mc = new ManagedComputer();
            //Iterate through each service registered with the WMI Provider.
            String nume = "Servicii Disponibile: ";
            String numeServ = String.Empty;
            //MessageBox.Show(numeServer);
            foreach (Service svc1 in mc.Services)
            {
                nume += svc1.Name + ", ";
                if (svc1.Name.Contains(numeServer))
                {
                    numeServ = svc1.Name;
                    break;
                }
            }

            if (numeServ == String.Empty)
                return;
            Label1.Text = nume;
            //Reference the Microsoft SQL Server service.
            Service svc = mc.Services[numeServ];
            //Stop the service if it is running and report on the status continuously until it has stopped.
            if (svc.ServiceState == ServiceState.Running)
            {
                if (svc.AcceptsStop)
                {
                    svc.Stop();

                    Label1.Text = String.Format("{0} service state is {1}", svc.Name, svc.ServiceState);
                    int cate = 0;
                    while (svc.ServiceState == ServiceState.Running && cate < 10)
                    {
                        Label1.Text = String.Format("{0} service state is {1}", svc.Name, svc.ServiceState);
                        svc.Refresh();
                        //svc.Stop();
                        cate++;
                        Thread.Sleep(2000);
                    }
                    Label1.Text = String.Format("{0} service state is {1}", svc.Name, svc.ServiceState);

                    //Start the service and report on the status continuously until it has started.
                    if (svc.ServiceState == ServiceState.Stopped)
                    {
                        svc.Start();
                        while (svc.ServiceState == ServiceState.Stopped)
                        {
                            Label1.Text = String.Format("{0} service state is {1}", svc.Name, svc.ServiceState);
                            svc.Refresh();
                        }
                        Label1.Text = String.Format("{0} service state is {1}", svc.Name, svc.ServiceState);
                    }
                }
            }
            else
                Label1.Text = "SQL Server service is not running.";
             * */
        }

      
        public Int32 DBVersion = 0;
        public Int32 DBLocalVersion = 0;

        
        private void BlockDataBase(SqlConnection conn, Boolean block)
        {
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                Trace.WriteLine("Eroare GetDataBaseVersion - conexiune intrerupta!");
                Trace.Flush();
                return;
            }
            SqlCommand cmd = new SqlCommand("Update [_DBProprietati] SET [IsUpdate]=" + (block ? "1" : "0") + ",[DataStartUpdate]=getdate() WHERE [PK]=0", conn);
            try
            {
                cmd.ExecuteScalar();
            }
            catch
            {

            }
        }

        private Int32 GetDataBaseVersion(SqlConnection conn)
        {
            Int32 ret = -1;
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                Trace.WriteLine("Eroare GetDataBaseVersion - conexiune intrerupta!");
                Trace.Flush();
                return ret;
            }
            SqlCommand cmd = new SqlCommand("SELECT [Versiune],[IsUpdate],[DataStartUpdate],DATEDIFF(SECOND,[DataStartUpdate] ,getdate()) secunde FROM [_DBProprietati] WHERE [PK]=0", conn);
            SqlDataReader dr = null;
            try
            {
                dr = cmd.ExecuteReader();
            }
            catch(Exception ex)
            {
                Trace.WriteLine(ex.Message);
                Trace.Flush();
                return ret;
            }
            if (!dr.HasRows)
            {
                dr.Close();
                Trace.WriteLine("Eroare GetDataBaseVersion - lipsa inregistrare versiune!");
                Trace.Flush();
                return ret;
            }
            dr.Read();
            Boolean asteptam = true;
            while (asteptam)
            {
                while (Convert.ToBoolean(dr["IsUpdate"]) == true && (Convert.ToInt32(dr["secunde"]) < 60))
                {
                    Label2.Text = "Baza de date - blocata de un update. Va rugam asteptati!";
                    Application.DoEvents();
                    Thread.Sleep(10000);
                    ret = Convert.ToInt32(dr["Versiune"]);
                    if (!dr.IsClosed)
                        dr.Close();
                    dr = cmd.ExecuteReader();
                    dr.Read();
                }
                if (Convert.ToBoolean(dr["IsUpdate"]))
                {
                    if (MessageBox.Show("Baza de date este blocata de alt utilizator pentru modificari.\nAsteptam terminarea modificarilor?", "Continuare", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
                    {
                        asteptam = false;
                        ret = Convert.ToInt32(dr["Versiune"]);
                        if (!dr.IsClosed)
                            dr.Close();
                        BlockDataBase(conn, false);
                    }
                    else
                    {
                        Thread.Sleep(60000);
                        ret = Convert.ToInt32(dr["Versiune"]);
                        if (!dr.IsClosed)
                            dr.Close();
                        dr = cmd.ExecuteReader();
                        dr.Read();
                    }
                }
                else
                {
                    ret = Convert.ToInt32(dr["Versiune"]);
                    asteptam = false;
                }

            }

            if (!dr.IsClosed)
                dr.Close();

            return ret;
        }
 
    }
}